﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using PS.DBUtility;
using System.Collections.Generic;
using System.Collections;
namespace PS.DAL
{
    /// <summary>
    /// 数据访问类:ExamQuestions
    /// </summary>
    public partial class ExamQuestions
    {
        public ExamQuestions()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录

        /// </summary>
        public bool Exists(int ExamQuestionsID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ExamQuestions");
            strSql.Append(" where ExamQuestionsID=@ExamQuestionsID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamQuestionsID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamQuestionsID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据

        /// </summary>
        public int Add(PS.Model.ExamQuestions model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamQuestions(");
            strSql.Append("WorkType,WorkLevel,ExamType,ExamContent,AnswerCount,CorrectAnswer,PutInDate,Creator,CreateTime,Editor,EditTime,DispOrder,Valid,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8,Spare9)");
            strSql.Append(" values (");
            strSql.Append("@WorkType,@WorkLevel,@ExamType,@ExamContent,@AnswerCount,@CorrectAnswer,@PutInDate,@Creator,@CreateTime,@Editor,@EditTime,@DispOrder,@Valid,@ValidMark,@Spare1,@Spare2,@Spare3,@Spare4,@SpareDecimal5,@SpareDecimal6,@SpareDecimal7,@SpareDecimal8,@Spare9)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@WorkType", SqlDbType.Int,4),
					new SqlParameter("@WorkLevel", SqlDbType.Int,4),
					new SqlParameter("@ExamType", SqlDbType.Int,4),
					new SqlParameter("@ExamContent", SqlDbType.NVarChar,4000),
					new SqlParameter("@AnswerCount", SqlDbType.Int,4),
					new SqlParameter("@CorrectAnswer", SqlDbType.NVarChar,4000),
					new SqlParameter("@PutInDate", SqlDbType.DateTime),
					new SqlParameter("@Creator", SqlDbType.VarChar,20),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Editor", SqlDbType.VarChar,20),
					new SqlParameter("@EditTime", SqlDbType.DateTime),
					new SqlParameter("@DispOrder", SqlDbType.Int,4),
					new SqlParameter("@Valid", SqlDbType.Bit,1),
					new SqlParameter("@ValidMark", SqlDbType.Int,4),
					new SqlParameter("@Spare1", SqlDbType.VarChar,200),
					new SqlParameter("@Spare2", SqlDbType.VarChar,200),
					new SqlParameter("@Spare3", SqlDbType.VarChar,200),
					new SqlParameter("@Spare4", SqlDbType.VarChar,200),
					new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9),
					new SqlParameter("@Spare9", SqlDbType.VarBinary)};
            parameters[0].Value = model.WorkType;
            parameters[1].Value = model.WorkLevel;
            parameters[2].Value = model.ExamType;
            parameters[3].Value = model.ExamContent;
            parameters[4].Value = model.AnswerCount;
            parameters[5].Value = model.CorrectAnswer;
            parameters[6].Value = model.PutInDate;
            parameters[7].Value = model.Creator;
            parameters[8].Value = model.CreateTime;
            parameters[9].Value = model.Editor;
            parameters[10].Value = model.EditTime;
            parameters[11].Value = model.DispOrder;
            parameters[12].Value = model.Valid;
            parameters[13].Value = model.ValidMark;
            parameters[14].Value = model.Spare1;
            parameters[15].Value = model.Spare2;
            parameters[16].Value = model.Spare3;
            parameters[17].Value = model.Spare4;
            parameters[18].Value = model.SpareDecimal5;
            parameters[19].Value = model.SpareDecimal6;
            parameters[20].Value = model.SpareDecimal7;
            parameters[21].Value = model.SpareDecimal8;
            parameters[22].Value = model.Spare9;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 导入试题数据
        /// </summary>
        /// <returns>-1:数据格式错误，－2：导入数据时产生错误，0:无数据，大于0，导入数据的条数</returns>
        public int Import(DataTable m_Dt, out int SuccessInsertCount, string CurrentUser)
        {
            StringBuilder StrB = new StringBuilder();
            int ErrRecordCount = 0;
            SuccessInsertCount = 0;

            Hashtable HasTab = new Hashtable();
            List<PS.DBUtility.TranSql> TranSql = new List<TranSql>();
            if (m_Dt != null && m_Dt.Rows.Count > 2)
            {
                int RowCount = m_Dt.Rows.Count;
                PS.Model.ExamQuestions EqModel = new PS.Model.ExamQuestions();
                int? WorkType = null;
                int? WorkLevel = null;
                int? AnswerCount = 0;

                StrB.Append("insert into ExamQuestions(");
                StrB.Append("WorkType,WorkLevel,ExamType,ExamContent,AnswerCount,CorrectAnswer,PutInDate,ValidMark,Creator,CreateTime)");
                StrB.Append(" values (");
                StrB.Append("@WorkType,@WorkLevel,@ExamType,@ExamContent,@AnswerCount,@CorrectAnswer,@PutInDate,@ValidMark,@Creator,@CreateTime)");
                int ExamType = 0;
                string CorrectAnswer = "1";
                string ExamContent = string.Empty;
                for (int i = 3; i < RowCount; i++)
                {
                    try
                    {
                        if (!string.IsNullOrEmpty(m_Dt.Rows[i][1].ToString().Trim()) && !string.IsNullOrEmpty(m_Dt.Rows[i][2].ToString().Trim()) && !string.IsNullOrEmpty(m_Dt.Rows[i][3].ToString().Trim()))
                        {
                            GetExamParas(m_Dt.Rows[i][0].ToString(), m_Dt.Rows[i][1].ToString(), m_Dt.Rows[i][2].ToString(), out WorkType, out WorkLevel, out ExamType);

                            if (WorkType == 0 || WorkLevel == 0 || ExamType == 0)
                            {
                                ErrRecordCount++;
                                continue;
                            }

                            AnswerCount = (m_Dt.Rows[i][4] != null && !string.IsNullOrEmpty(m_Dt.Rows[i][4].ToString())) ? int.Parse(m_Dt.Rows[i][4].ToString()) : 4;
                            if (AnswerCount > 0 && m_Dt.Rows[i][5] != null && m_Dt.Rows[i][5].ToString().Trim().Length > 0)
                            {
                                EqModel.WorkType = WorkType;
                                EqModel.WorkLevel = WorkLevel;
                                EqModel.ExamType = ExamType;

                                if (m_Dt.Rows[i][2].ToString().Trim().Equals("判断题"))
                                {
                                    if (m_Dt.Rows[i][5].ToString().Trim().Contains("对") || m_Dt.Rows[i][5].ToString().Trim().Contains("错"))
                                    {
                                        AnswerCount = 2;
                                        if (m_Dt.Rows[i][5].ToString().Trim().Contains("对"))
                                        {

                                            CorrectAnswer = "1";
                                        }
                                        else
                                        {
                                            CorrectAnswer = "0";
                                        }
                                        EqModel.AnswerCount = AnswerCount;
                                        EqModel.CorrectAnswer = CorrectAnswer;
                                    }
                                    else
                                    {
                                        ErrRecordCount++;
                                        continue;
                                    }
                                }
                                else
                                {
                                    //AnswerCount = int.Parse(m_Dt.Rows[i][4].ToString());
                                    CorrectAnswer = m_Dt.Rows[i][5].ToString().Trim();
                                }

                                EqModel.ExamContent = m_Dt.Rows[i][3].ToString();
                                EqModel.ExamContent = EqModel.ExamContent.Replace("\n", "</br>");

                                EqModel.AnswerCount = AnswerCount;
                                EqModel.CorrectAnswer = CorrectAnswer;
                                EqModel.CorrectAnswer = EqModel.CorrectAnswer.Replace("\n", "</br>");

                                //EqModel.PutInDate =  DateTime.Parse(m_Dt.Rows[i][6]);
                                EqModel.ValidMark = (m_Dt.Rows[i][7] != null) ? (GetQuestionValidMark(m_Dt.Rows[i][7].ToString())) : 1;
                                EqModel.Creator = CurrentUser;

                                DateTime dtInput = System.DateTime.Now;

                                EqModel.CreateTime = dtInput;
                                SqlParameter[] parameters = {
					            new SqlParameter("@WorkType", SqlDbType.Int,4),
					            new SqlParameter("@WorkLevel", SqlDbType.Int,4),
					            new SqlParameter("@ExamType", SqlDbType.Int,4),
					            new SqlParameter("@ExamContent", SqlDbType.NVarChar,4000),
					            new SqlParameter("@AnswerCount", SqlDbType.Int,4),
					            new SqlParameter("@CorrectAnswer", SqlDbType.NVarChar,4000),
					            new SqlParameter("@PutInDate", SqlDbType.VarChar,21),
					            new SqlParameter("@ValidMark", SqlDbType.Int,4),
					            new SqlParameter("@Creator", SqlDbType.VarChar,20),
					            new SqlParameter("@CreateTime", SqlDbType.DateTime)};
                                parameters[0].Value = EqModel.WorkType;
                                parameters[1].Value = EqModel.WorkLevel;
                                parameters[2].Value = EqModel.ExamType;
                                parameters[3].Value = EqModel.ExamContent;
                                parameters[4].Value = EqModel.AnswerCount;
                                parameters[5].Value = EqModel.CorrectAnswer;
                                parameters[6].Value = dtInput.ToString("yyyy-MM-dd");
                                parameters[7].Value = EqModel.ValidMark;
                                parameters[8].Value = EqModel.Creator;
                                parameters[9].Value = EqModel.CreateTime;

                                PS.DBUtility.TranSql TSql = new TranSql();
                                TSql.SqlKey = StrB.ToString();
                                TSql.SqlValue = parameters;
                                TranSql.Add(TSql);
                                SuccessInsertCount++;
                                //SuccessInsertCount++;
                                if (ErrRecordCount > 0)
                                {
                                    TranSql.Clear();
                                    break;
                                }

                            }
                            else
                            {
                                ErrRecordCount++;
                            }
                        }
                    }
                    catch
                    {
                        ErrRecordCount++;
                    }
                }

                if (ErrRecordCount == 0)
                {
                    PS.DBUtility.DbHelperSQL.ExecuteSqlTran(TranSql);
                }
            }
            return ErrRecordCount;
        }


        public int GetQuestionValidMark(string ValidMark)
        {
            int result = 1;

            switch (ValidMark)
            {
                case "有效":
                case "1":
                    result = 1; break;
                case "无效":
                case "0":
                    result = 0; break;

            }

            return result;
        }

        DataSet Ds = null;
        private void GetExamParas(string WorkType, string WorkLevel, string ExamType, out int? intWorkType, out int? intWorkLevel, out int intExamType)
        {
            intWorkType = 0;
            intWorkLevel = 0;
            intExamType = 0;
            if (Ds == null || Ds.Tables.Count != 3)
            {
                StringBuilder StrB = new StringBuilder();
                StrB.Append("select id,keyvalue from dbo.SysDictionarySub where mainkeyid = (select mainkeyid from dbo.SysDictionaryMain where keyname = 'ksxt_gz');");
                StrB.Append("select id,keyvalue from dbo.SysDictionarySub where mainkeyid = (select mainkeyid from dbo.SysDictionaryMain where keyname = 'ksxt_gzjb');");
                StrB.Append("select id,keyvalue from dbo.SysDictionarySub where mainkeyid = (select mainkeyid from dbo.SysDictionaryMain where keyname = 'ksxt_stlx');");
                Ds = PS.DBUtility.DbHelperSQL.Query(StrB.ToString());
            }

            DataRow[] DrWorkType = Ds.Tables[0].Select(" KeyValue='" + WorkType + "'");
            DataRow[] DrWorkLevel = Ds.Tables[1].Select(" KeyValue='" + WorkLevel + "'");
            DataRow[] DrExamType = Ds.Tables[2].Select(" KeyValue='" + ExamType + "'");
            if (DrWorkType != null && DrWorkType.Length > 0) intWorkType = int.Parse(DrWorkType[0]["ID"].ToString());
            if (DrWorkLevel != null && DrWorkLevel.Length > 0) intWorkLevel = int.Parse(DrWorkLevel[0]["ID"].ToString());
            if (DrExamType != null && DrExamType.Length > 0) intExamType = int.Parse(DrExamType[0]["ID"].ToString());
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(PS.Model.ExamQuestions model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamQuestions set ");
            strSql.Append("WorkType=@WorkType,");
            strSql.Append("WorkLevel=@WorkLevel,");
            strSql.Append("ExamType=@ExamType,");
            strSql.Append("ExamContent=@ExamContent,");
            strSql.Append("AnswerCount=@AnswerCount,");
            strSql.Append("CorrectAnswer=@CorrectAnswer,");
            strSql.Append("PutInDate=@PutInDate,");
            strSql.Append("Editor=@Editor,");
            strSql.Append("EditTime=@EditTime,");
            strSql.Append("DispOrder=@DispOrder,");
            strSql.Append("Valid=@Valid,");
            strSql.Append("ValidMark=@ValidMark,");
            strSql.Append("Spare1=@Spare1,");
            strSql.Append("Spare2=@Spare2,");
            strSql.Append("Spare3=@Spare3,");
            strSql.Append("Spare4=@Spare4,");
            strSql.Append("SpareDecimal5=@SpareDecimal5,");
            strSql.Append("SpareDecimal6=@SpareDecimal6,");
            strSql.Append("SpareDecimal7=@SpareDecimal7,");
            strSql.Append("SpareDecimal8=@SpareDecimal8,");
            strSql.Append("Spare9=@Spare9");
            strSql.Append(" where ExamQuestionsID=@ExamQuestionsID");
            SqlParameter[] parameters = {
					new SqlParameter("@WorkType", SqlDbType.Int,4),
					new SqlParameter("@WorkLevel", SqlDbType.Int,4),
					new SqlParameter("@ExamType", SqlDbType.Int,4),
					new SqlParameter("@ExamContent", SqlDbType.NVarChar,4000),
					new SqlParameter("@AnswerCount", SqlDbType.Int,4),
					new SqlParameter("@CorrectAnswer", SqlDbType.NVarChar,4000),
					new SqlParameter("@PutInDate", SqlDbType.DateTime),
					new SqlParameter("@Editor", SqlDbType.VarChar,20),
					new SqlParameter("@EditTime", SqlDbType.DateTime),
					new SqlParameter("@DispOrder", SqlDbType.Int,4),
					new SqlParameter("@Valid", SqlDbType.Bit,1),
					new SqlParameter("@ValidMark", SqlDbType.Int,4),
					new SqlParameter("@Spare1", SqlDbType.VarChar,200),
					new SqlParameter("@Spare2", SqlDbType.VarChar,200),
					new SqlParameter("@Spare3", SqlDbType.VarChar,200),
					new SqlParameter("@Spare4", SqlDbType.VarChar,200),
					new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9),
					new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9),
					new SqlParameter("@Spare9", SqlDbType.VarBinary),
					new SqlParameter("@ExamQuestionsID", SqlDbType.Int,4)};
            parameters[0].Value = model.WorkType;
            parameters[1].Value = model.WorkLevel;
            parameters[2].Value = model.ExamType;
            parameters[3].Value = model.ExamContent;
            parameters[4].Value = model.AnswerCount;
            parameters[5].Value = model.CorrectAnswer;
            parameters[6].Value = model.PutInDate;
            parameters[7].Value = model.Editor;
            parameters[8].Value = model.EditTime;
            parameters[9].Value = model.DispOrder;
            parameters[10].Value = model.Valid;
            parameters[11].Value = model.ValidMark;
            parameters[12].Value = model.Spare1;
            parameters[13].Value = model.Spare2;
            parameters[14].Value = model.Spare3;
            parameters[15].Value = model.Spare4;
            parameters[16].Value = model.SpareDecimal5;
            parameters[17].Value = model.SpareDecimal6;
            parameters[18].Value = model.SpareDecimal7;
            parameters[19].Value = model.SpareDecimal8;
            parameters[20].Value = model.Spare9;
            parameters[21].Value = model.ExamQuestionsID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据

        /// </summary>
        public bool Delete(int ExamQuestionsID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamQuestions set ValidMark=0 ");
            strSql.Append(" where ExamQuestionsID=@ExamQuestionsID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamQuestionsID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamQuestionsID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据

        /// </summary>
        public bool DeleteList(string ExamQuestionsIDlist)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update ExamQuestions set ValidMark=0 ");
            strSql.Append(" where ExamQuestionsID in (" + ExamQuestionsIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体

        /// </summary>
        public PS.Model.ExamQuestions GetModel(int ExamQuestionsID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ExamQuestionsID,WorkType,WorkLevel,ExamType,ExamContent,AnswerCount,CorrectAnswer,PutInDate,Creator,CreateTime,Editor,EditTime,DispOrder,Valid,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8,Spare9 from ExamQuestions ");
            strSql.Append(" where ExamQuestionsID=@ExamQuestionsID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamQuestionsID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamQuestionsID;

            PS.Model.ExamQuestions model = new PS.Model.ExamQuestions();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            return GetModelByDataSet(ds);
        }

        /// <summary>
        /// 从DataSet得到一个对象实体 
        /// </summary>
        public PS.Model.ExamQuestions GetModelByDataSet(DataSet ds)
        {

            if (ds.Tables[0].Rows.Count > 0)
            {
                return GetModelByDataRow(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 从DataRow得到一个对象实体 
        /// </summary>
        public PS.Model.ExamQuestions GetModelByDataRow(DataRow dr)
        {
            PS.Model.ExamQuestions model = new PS.Model.ExamQuestions();
            if (dr["ExamQuestionsID"].ToString() != "")
            {
                model.ExamQuestionsID = int.Parse(dr["ExamQuestionsID"].ToString());
            }
            if (dr["WorkType"].ToString() != "")
            {
                model.WorkType = int.Parse(dr["WorkType"].ToString());
            }
            if (dr["WorkLevel"].ToString() != "")
            {
                model.WorkLevel = int.Parse(dr["WorkLevel"].ToString());
            }
            if (dr["ExamType"].ToString() != "")
            {
                model.ExamType = int.Parse(dr["ExamType"].ToString());
            }
            model.ExamContent = dr["ExamContent"].ToString();
            if (dr["AnswerCount"].ToString() != "")
            {
                model.AnswerCount = int.Parse(dr["AnswerCount"].ToString());
            }
            model.CorrectAnswer = dr["CorrectAnswer"].ToString();
            if (dr["PutInDate"].ToString() != "")
            {
                model.PutInDate = DateTime.Parse(dr["PutInDate"].ToString());
            }
            model.Creator = dr["Creator"].ToString();
            if (dr["CreateTime"].ToString() != "")
            {
                model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
            }
            model.Editor = dr["Editor"].ToString();
            if (dr["EditTime"].ToString() != "")
            {
                model.EditTime = DateTime.Parse(dr["EditTime"].ToString());
            }
            if (dr["DispOrder"].ToString() != "")
            {
                model.DispOrder = int.Parse(dr["DispOrder"].ToString());
            }
            if (dr["Valid"].ToString() != "")
            {
                if ((dr["Valid"].ToString() == "1") || (dr["Valid"].ToString().ToLower() == "true"))
                {
                    model.Valid = true;
                }
                else
                {
                    model.Valid = false;
                }
            }
            if (dr["ValidMark"].ToString() != "")
            {
                model.ValidMark = int.Parse(dr["ValidMark"].ToString());
            }
            model.Spare1 = dr["Spare1"].ToString();
            model.Spare2 = dr["Spare2"].ToString();
            model.Spare3 = dr["Spare3"].ToString();
            model.Spare4 = dr["Spare4"].ToString();
            if (dr["SpareDecimal5"].ToString() != "")
            {
                model.SpareDecimal5 = decimal.Parse(dr["SpareDecimal5"].ToString());
            }
            if (dr["SpareDecimal6"].ToString() != "")
            {
                model.SpareDecimal6 = decimal.Parse(dr["SpareDecimal6"].ToString());
            }
            if (dr["SpareDecimal7"].ToString() != "")
            {
                model.SpareDecimal7 = decimal.Parse(dr["SpareDecimal7"].ToString());
            }
            if (dr["SpareDecimal8"].ToString() != "")
            {
                model.SpareDecimal8 = decimal.Parse(dr["SpareDecimal8"].ToString());
            }
            if (dr["Spare9"].ToString() != "")
            {
                model.Spare9 = (byte[])dr["Spare9"];
            }
            return model;

        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ExamQuestionsID,WorkType,WorkLevel,ExamType,ExamContent,AnswerCount,CorrectAnswer,PutInDate,Creator,CreateTime,Editor,EditTime,DispOrder,Valid,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8,Spare9 ");
            strSql.Append(" FROM ExamQuestions ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得前几行数据 
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ExamQuestionsID,WorkType,WorkLevel,ExamType,ExamContent,AnswerCount,CorrectAnswer,PutInDate,Creator,CreateTime,Editor,EditTime,DispOrder,Valid,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8,Spare9 ");
            strSql.Append(" FROM ExamQuestions ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据条件获取考题信息
        /// </summary>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="ExamType"></param>
        /// <param name="ExamContent"></param>
        /// <param name="Valid"></param>
        /// <param name="s_b"></param>
        /// <param name="s_e"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet GetList(string WorkType, string WorkLevel, string ExamType, string ExamContent, string Valid, string s_b, string s_e, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select a.*,b.KeyValue WorkTypeValue ,c.KeyValue WorkLevelValue , ");
            strSql.Append("d.KeyValue ExamTypeValue ,CorrectAnswerValue = case when CorrectAnswer='1' ");
            strSql.Append("then '对' when CorrectAnswer='0' then '错' else CorrectAnswer end,");
            strSql.Append("ValidValue =case when Valid=1 then '是' else '否' end ");
            strSql.Append(", PutInDateValue= convert(varchar(10),PutInDate,121)  ");
            strSql.Append("from dbo.ExamQuestions a ");
            strSql.Append(" left join dbo.SysDictionarySub b on a.WorkType=b.id and b.MainKeyID=(select MainKeyID from dbo.SysDictionaryMain m where  m.KeyName='ksxt_gz')");
            strSql.Append(" left join dbo.SysDictionarySub c on a.WorkLevel=c.id and c.MainKeyID=(select MainKeyID from dbo.SysDictionaryMain m where  m.KeyName='ksxt_gzjb')");
            strSql.Append(" left join dbo.SysDictionarySub d on a.ExamType=d.id and d.MainKeyID=(select MainKeyID from dbo.SysDictionaryMain m where  m.KeyName='ksxt_stlx')");
            strSql.Append(" where a.ValidMark != 0 ");


            if (!string.IsNullOrEmpty(WorkType))
            {
                strSql.Append(" and a.WorkType =").Append(WorkType);
            }
            if (!string.IsNullOrEmpty(WorkLevel))
            {
                strSql.Append(" and a.WorkLevel =").Append(WorkLevel);
            }
            if (!string.IsNullOrEmpty(ExamType))
            {
                strSql.Append(" and a.ExamType =").Append(ExamType);
            }


            if (!string.IsNullOrEmpty(ExamContent))
            {
                strSql.Append(" and a.ExamContent like '%").Append(ExamContent).Append("%'");
            }


            if (!string.IsNullOrEmpty(Valid))
            {
                strSql.Append(" and a.Valid = ").Append(Valid);
            }


            if (!string.IsNullOrEmpty(s_b) && !string.IsNullOrEmpty(s_e))
            {
                strSql.Append(" and a.PutInDate between '").Append(s_b).Append("' and '").Append(s_e).Append("'");
            }
            else
            {
                if (!string.IsNullOrEmpty(s_b))
                {
                    strSql.Append(" and a.PutInDate >= '").Append(s_b).Append("'");
                }
                if (!string.IsNullOrEmpty(s_e))
                {
                    strSql.Append(" and a.PutInDate <= '").Append(s_e).Append("'");
                }
            }
            strSql.Append("  order by CreateTime desc");
            string totalSql = PageSplitHelper.GetTotalSql(strSql.ToString());
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            string pageSql = PageSplitHelper.GetPageSql(strSql.ToString(), pageSize, pageIndex, totalCount);

            return DbHelperSQL.Query(pageSql);
        }

        /// <summary>
        /// 设置为有效
        /// </summary>
        public bool SetValid(string ExamQuestionsIDlist)
        {

            StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from ExamQuestions ");

            strSql.Append("update ExamQuestions set Valid=1 ");
            strSql.Append(" where ExamQuestionsID in (" + ExamQuestionsIDlist + ")  ");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 设置为无效
        /// </summary>
        public bool SetUnValid(string ExamQuestionsIDlist)
        {

            StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from ExamQuestions ");

            strSql.Append("update ExamQuestions set Valid=0 ");
            strSql.Append(" where ExamQuestionsID in (" + ExamQuestionsIDlist + ")  ");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "ExamQuestions";
            parameters[1].Value = "ExamQuestionsID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        public int GetQuestionsCountBy(int QuestionType, int WorkType, int WorkLevel)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from ExamQuestions ");

            strSql.Append("select count(*) from  ExamQuestions where Valid=1 and ValidMark!=0 ");
            strSql.Append(((QuestionType == 0) ? "" : " and ExamType='" + QuestionType + "' ") +
               ((WorkType == 0) ? "" : (" and WorkType='" + WorkType + "' ")) + ((WorkLevel == 0) ? "" : (" and WorkLevel='" + WorkLevel + "' ")));
            object counts = DbHelperSQL.ExecuteScalar(strSql.ToString());
            int result = 0;
            try
            { result = Convert.ToInt32(counts); }
            catch
            { result = 0; }
            return result;
        }

        /// <summary>
        /// 管理专业题量(刘小芳_2011-12-26)
        /// </summary>
        /// <param name="QuestionType"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <returns></returns>
        public int GetQuestionsCountByIsManager(int QuestionType, int WorkType, int WorkLevel)
        {
            int result = 0;
            StringBuilder strSql = new StringBuilder();
            DataTable dtWorktype = DbHelperSQL.Query("select id from dbo.SysDictionarySub where KeyValue =( select replace(KeyValue,'专业','') from SysDictionarySub where id='" + WorkLevel + "')").Tables[0];
            if (dtWorktype != null && dtWorktype.Rows.Count > 0)
            {
                string strWhereOther = ((WorkType == 0) ? " 1=1 " : " and WorkType='" + dtWorktype.Rows[0]["ID"] + "'") +
                            ((WorkLevel == 0) ? "" : " and WorkLevel in(select top 5 a.id from dbo.SysDictionarySub a, SysDictionaryMain b where a.MainKeyID = b.MainKeyID and a.ValidMark = 1 and b.KeyName='ksxt_gzjb') and  ExamType in (" + QuestionType + ") and Valid=1 and   ValidMark=1 ");
                strSql.Append("select count(*) from  ExamQuestions where Valid=1 and ValidMark!=0 ");
                strSql.Append(strWhereOther);

                object counts = DbHelperSQL.ExecuteScalar(strSql.ToString());

                try
                { result = Convert.ToInt32(counts); }
                catch
                { result = 0; }
            }
            return result;
        }

        //随机选题
        public List<PS.Model.ExamQuestions> GetRandomQuestions(int QuestionType, int QuestionCount, int WorkType, int WorkLevel)
        {
            string strWhere = " ExamType='" + QuestionType + "' " + ((WorkType == 0) ? "" : " and WorkType='" + WorkType + "' ") +
            ((WorkLevel == 0) ? "" : " and WorkLevel='" + WorkLevel + "' ");
            DataSet ds = GetList(QuestionCount, strWhere, " newid() ");
            List<PS.Model.ExamQuestions> examQuestions = new List<PS.Model.ExamQuestions>();
            if (ds != null && ds.Tables[0] != null) // && ds.Tables[0].Rows > QuestionCount  处理题目太少
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    examQuestions.Add(GetModelByDataRow(dr));
                }
                return examQuestions;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  随机选题 限制题型
        /// </summary>
        /// <param name="QuestionType"></param>
        /// <param name="QuestionCount"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <returns></returns>
        //public List<PS.Model.ExamQuestions> GetRandomQuestionsByQuestionType(string QuestionType, int QuestionCount, int WorkType, int WorkLevel)
        //{
        //    string strWhere = ((WorkType == 0) ? " 1=1 " : "  ( WorkType='" + WorkType + "' or Isnull ( WorkType,'')=''  or  WorkType=0 ) ") +
        //    ((WorkLevel == 0) ? "" : " and WorkLevel='" + WorkLevel + "'  and  ExamType in (" + QuestionType + ") and Valid=1 and   ValidMark=1 ");
        //    DataSet ds = GetList(QuestionCount, strWhere, " newid() ");
        //    List<PS.Model.ExamQuestions> examQuestions = new List<PS.Model.ExamQuestions>();
        //    if (ds != null && ds.Tables[0] != null) // && ds.Tables[0].Rows > QuestionCount  处理题目太少
        //    {
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            examQuestions.Add(GetModelByDataRow(dr));
        //        }
        //        return examQuestions;
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}


        public void GetRandomQuestions(List<PS.Model.ExamQuestions> examQuestions, List<PS.Model.ExamQuestionType> QuestionTypes, int WorkType, int WorkLevel, int TotalCount)
        {
            if (TotalCount == 0)
                return;
            string QuestionType = "";
            try
            {
                foreach (PS.Model.ExamQuestionType eqt in QuestionTypes)
                {
                    if (eqt.QuestionCount > 0)
                        QuestionType += "'" + eqt.QuestionType + "',";
                }
                QuestionType = QuestionType.TrimEnd(',');

                string strWhere = ((WorkType == 0) ? " 1=1 " : "  ( WorkType='" + WorkType + "' or Isnull( WorkType,'')=''  or  WorkType=0 ) ") +
                    ((WorkLevel == 0) ? "" : " and WorkLevel='" + WorkLevel + "'  and  ExamType in (" + QuestionType + ") and Valid=1 and   ValidMark=1 ");
                DataSet ds = GetList(0, strWhere, " newid() ");//全部题，筛选
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) // && ds.Tables[0].Rows > QuestionCount  处理题目太少
                {
                    List<PS.Model.ExamQuestions> eqs = new List<PS.Model.ExamQuestions>();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        PS.Model.ExamQuestions eq = GetModelByDataRow(dr);
                        foreach (PS.Model.ExamQuestionType eqt in QuestionTypes)
                        {
                            if (eqt.QuestionCount > 0 && eqt.QuestionType == eq.ExamType)
                            {
                                eqt.QuestionCount = eqt.QuestionCount - 1;
                                examQuestions.Add(eq);
                                TotalCount--;
                            }
                        }
                        if (TotalCount <= 0)
                            break;
                    }

                }
                else
                {
                    //没有题目
                }
            }
            catch (Exception ex)
            {
                string a = ex.Message;
            }

        }

        /// <summary>
        /// 管理员抽题原则:
        ///       在参数中设置的专业知识中对应当前登录人的工种等级进行抽题(**专业)
        ///       如果专业知识不够则在对应的工种上进行抽题(将'专业'二字去掉,与工种进行匹配)
        ///       如果工种中也抽不到题,则就只考管理学知识和公文写作
        /// </summary>
        /// <param name="examQuestions"></param>
        /// <param name="QuestionTypes"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="TotalCount"></param>
        public void GetRandomQuestionsByIsManager(List<PS.Model.ExamQuestions> examQuestions, List<PS.Model.ExamQuestionType> QuestionTypes, int WorkType, int WorkLevel, int TotalCount)
        {
            if (TotalCount == 0)
                return;
            string QuestionType = "";
            try
            {
                foreach (PS.Model.ExamQuestionType eqt in QuestionTypes)
                {
                    if (eqt.QuestionCount > 0)
                        QuestionType += "'" + eqt.QuestionType + "',";
                }
                QuestionType = QuestionType.TrimEnd(',');

                string strWhere = ((WorkType == 0) ? " 1=1 " : "  ( WorkType='" + WorkType + "' or Isnull( WorkType,'')=''  or  WorkType=0 ) ") +
                    ((WorkLevel == 0) ? "" : " and WorkLevel='" + WorkLevel + "'  and  ExamType in (" + QuestionType + ") and Valid=1 and   ValidMark=1 ");
                DataSet ds = GetList(0, strWhere, " newid() ");//全部题，筛选
                //专业题目
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) // && ds.Tables[0].Rows > QuestionCount  处理题目太少
                {
                    List<PS.Model.ExamQuestions> eqs = new List<PS.Model.ExamQuestions>();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        PS.Model.ExamQuestions eq = GetModelByDataRow(dr);
                        foreach (PS.Model.ExamQuestionType eqt in QuestionTypes)
                        {
                            if (eqt.QuestionCount > 0 && eqt.QuestionType == eq.ExamType)
                            {
                                eqt.QuestionCount = eqt.QuestionCount - 1;
                                examQuestions.Add(eq);
                                TotalCount--;
                            }
                        }
                        if (TotalCount <= 0)
                            break;
                    }
                }

                if (TotalCount > 0) //专业题目不够
                {
                    DataTable dtWorktype = DbHelperSQL.Query("select id from dbo.SysDictionarySub where KeyValue =( select replace(KeyValue,'专业','') from SysDictionarySub where id='" + WorkLevel + "')").Tables[0];
                    if (dtWorktype != null && dtWorktype.Rows.Count > 0)
                    {
                        string strWhereOther = ((WorkType == 0) ? " 1=1 " : " WorkType='" + dtWorktype.Rows[0]["ID"] + "'") +
                        ((WorkLevel == 0) ? "" : " and WorkLevel in(select top 5 a.id from dbo.SysDictionarySub a, SysDictionaryMain b where a.MainKeyID = b.MainKeyID and a.ValidMark = 1 and b.KeyName='ksxt_gzjb') and  ExamType in (" + QuestionType + ") and Valid=1 and   ValidMark=1 ");
                        DataSet dsOther = GetList(0, strWhereOther, " newid() ");//全部题，筛选
                        List<PS.Model.ExamQuestions> eqsOther = new List<PS.Model.ExamQuestions>();
                        if (dsOther != null && dsOther.Tables[0] != null && dsOther.Tables[0].Rows.Count > 0) // && ds.Tables[0].Rows > QuestionCount  处理题目太少
                        {
                            foreach (DataRow dr in dsOther.Tables[0].Rows)
                            {
                                PS.Model.ExamQuestions eqOther = GetModelByDataRow(dr);
                                foreach (PS.Model.ExamQuestionType eqtOther in QuestionTypes)
                                {
                                    if (eqtOther.QuestionCount > 0 && eqtOther.QuestionType == eqOther.ExamType)
                                    {
                                        eqtOther.QuestionCount = eqtOther.QuestionCount - 1;
                                        examQuestions.Add(eqOther);
                                        TotalCount--;
                                    }
                                }
                                if (TotalCount <= 0)
                                    break;
                            }
                        }
                        else
                        {
                            //没有题目
                        }
                    }
                    else
                    {
                        //没有专业对应的工种
                    }
                }

            }
            catch (Exception ex)
            {
                string a = ex.Message;
            }

        }


        /// <summary>
        /// 随机选题 包含越级抽题 空工种题为所有人都考
        /// </summary>
        /// <param name="ExamQuestionTypes">考题类型参数</param>
        /// <param name="WorkType">工种</param>
        /// <param name="WorkLevel">工种级别</param>
        /// <param name="isManager">是否管理人员</param>
        /// <returns></returns>
        public List<PS.Model.ExamQuestions> GetRandomQuestions(List<PS.Model.ExamQuestionType> ExamQuestionTypes, int WorkType, int WorkLevel, bool isManager, int ExamSysParaID)
        {
            ExamQuestionType examQuestionTypeDAL = new ExamQuestionType();

            List<PS.Model.ExamQuestionType> QuestionTypes = examQuestionTypeDAL.GetListModel(" ExamSysParaID='" + ExamSysParaID + "' Order by disporder ");

            List<PS.Model.ExamQuestions> results = new List<PS.Model.ExamQuestions>();
            PS.Model.ExamSysParaWeight CurrentParaWeight = GetCurrentParaWeight(ExamSysParaID, WorkLevel, isManager);


            if (CurrentParaWeight == null)
            {
                GetRandomQuestions(results, ExamQuestionTypes, WorkType, WorkLevel, GetQuestionNum(ExamQuestionTypes));
            }
            else
            {
                switch (CurrentParaWeight.WeightType)
                {
                    case 1:  //初级  
                        GetRandomQuestions(results, QuestionTypes, WorkType, 181, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level2));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 182, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level3));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 183, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level4));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 184, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level5));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 185, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level6));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 186, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level7));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 187, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level8));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 188, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level9));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 180, GetQuestionNum(QuestionTypes));
                        break;
                    case 2:  //中级
                        GetRandomQuestions(results, QuestionTypes, WorkType, 180, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level1));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 182, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level3));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 183, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level4));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 184, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level5));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 185, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level6));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 186, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level7));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 187, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level8));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 188, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level9));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 181, GetQuestionNum(QuestionTypes));
                        break;
                    case 3:  //高级
                        GetRandomQuestions(results, QuestionTypes, WorkType, 180, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level1));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 181, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level2));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 183, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level4));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 184, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level5));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 185, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level6));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 186, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level7));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 187, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level8));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 188, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level9));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 182, GetQuestionNum(QuestionTypes));
                        break;
                    case 4:  //技师 
                        GetRandomQuestions(results, QuestionTypes, WorkType, 180, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level1));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 181, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level2));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 182, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level3));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 184, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level5));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 185, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level6));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 186, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level7));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 187, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level8));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 188, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level9));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 183, GetQuestionNum(QuestionTypes));
                        break;
                    case 5:  //高级技师
                        GetRandomQuestions(results, QuestionTypes, WorkType, 180, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level1));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 181, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level2));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 182, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level3));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 183, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level4));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 185, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level6));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 186, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level7));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 187, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level8));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 188, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.Level9));
                        GetRandomQuestions(results, QuestionTypes, WorkType, 184, GetQuestionNum(QuestionTypes));
                        break;
                    case 6:  //管理  
                        GetRandomQuestions(results, QuestionTypes, WorkType, 1801, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.ArticleWrite));//公文写作
                        GetRandomQuestionsByIsManager(results, QuestionTypes, WorkType, WorkLevel, GetQuestionNum(ExamQuestionTypes, CurrentParaWeight.SpecialKnowledge));//专业知识
                        GetRandomQuestions(results, QuestionTypes, WorkType, 189, GetQuestionNum(QuestionTypes));//管理学知识
                        break;
                }

            }

            return results;
        }

        public int GetQuestionNum(List<PS.Model.ExamQuestionType> ExamQuestionTypes)
        {
            return GetQuestionNum(ExamQuestionTypes, 100);
        }

        public int GetQuestionNum(List<PS.Model.ExamQuestionType> ExamQuestionTypes, decimal? Questionpercent)
        {
            int result = 0;
            foreach (PS.Model.ExamQuestionType eqt in ExamQuestionTypes)
            {
                if (eqt.QuestionCount.HasValue)
                    result += eqt.QuestionCount.Value;
            }
            return Convert.ToInt32((Convert.ToDecimal(result) * Questionpercent.Value) / 100);
        }



        /// <summary>
        /// 获取考试权重
        /// </summary>
        /// <param name="ExamSysParaID"></param>
        /// <param name="isManager"></param>
        /// <returns></returns>
        public PS.Model.ExamSysParaWeight GetCurrentParaWeight(int ExamSysParaID, int WorkLevel, bool isManager)
        {
            List<PS.Model.ExamSysParaWeight> ExamSysParaWeights = new List<PS.Model.ExamSysParaWeight>();
            ExamSysParaWeights = (new ExamSysParaWeight()).GetListModel(" ValidMark=1 and ExamSysParaID=" + ExamSysParaID + " Order by WeightType ");
            if (ExamSysParaWeights.Count <= 0)
            {
                return null;//没有权重
            }
            if (isManager)
                return ExamSysParaWeights[5];
            string SubKeyName = "";
            try
            {
                SubKeyName = (new SysDictionarySub()).GetModel(WorkLevel).SubKeyName;
            }
            catch (Exception ex)
            {
                return null;
            }
            if (SubKeyName.EndsWith("_cj"))
                return ExamSysParaWeights[0];
            else if (SubKeyName.EndsWith("_zj"))
                return ExamSysParaWeights[1];
            else if (SubKeyName.EndsWith("_gj"))
                return ExamSysParaWeights[2];
            else if (SubKeyName.EndsWith("_js"))
                return ExamSysParaWeights[3];
            else if (SubKeyName.EndsWith("_gjjs"))
                return ExamSysParaWeights[4];

            return null;
        }
        //抽题
        /// <summary>
        /// 包括权重的抽题
        /// </summary>
        /// <param name="examRecordModel"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="isManager"></param>
        public void SelectQuestions(PS.Model.ExamRecord examRecordModel, int WorkType, int WorkLevel, bool isManager)
        {
            StringBuilder strSQL = new StringBuilder();
            string ExamIDString = "";
            List<PS.Model.ExamQuestionType> ExamQuestionTypes = (new ExamQuestionType()).GetListModel(" ExamSysParaID='" + examRecordModel.ExamSysParaID.Value + "' Order by disporder ");

            List<PS.Model.ExamQuestions> SelectQuestions = new List<PS.Model.ExamQuestions>();
            SelectQuestions = GetRandomQuestions(ExamQuestionTypes, WorkType, WorkLevel, isManager, examRecordModel.ExamSysParaID.Value);
            List<PS.Model.ExamQuestions> SortQuestions = new List<PS.Model.ExamQuestions>();

            foreach (PS.Model.ExamQuestionType examQuestionTypeModel in ExamQuestionTypes)
            {
                StringBuilder strIDS = new StringBuilder();

                foreach (PS.Model.ExamQuestions eq in SelectQuestions)
                {
                    if (eq.ExamType == examQuestionTypeModel.QuestionType)
                    {
                        strIDS.Append(eq.ExamQuestionsID + ",");
                        SortQuestions.Add(eq);
                    }

                }
                if (strIDS.Length > 0)
                {
                    ExamIDString += strIDS.ToString();
                    strSQL.Append(" insert into ExamResult select " + examRecordModel.ExamRecordID + " as ExamRecordID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
                    strSQL.Append(" '" + examRecordModel.UserID + "' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
                    strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
                    strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where  ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")  ");
                }
            }
            examRecordModel.ExamIDString = ExamIDString.TrimEnd(',');
            ExamIDString = " update ExamRecord set ExamIDString='" + examRecordModel.ExamIDString + "' where ExamRecordID='" + examRecordModel.ExamRecordID + "' ";

            List<string> listStr = new List<string>();
            listStr.Add(strSQL.ToString());
            listStr.Add(ExamIDString);

            int rows = DbHelperSQL.ExecuteSqlTran(listStr);

        }
        //抽题
        public void SelectQuestions(List<PS.Model.ExamQuestionType> examQuestionTypes, PS.Model.ExamRecord examRecordModel, int WorkType, int WorkLevel, bool isManager)
        {
            StringBuilder strSQL = new StringBuilder();
            string ExamIDString = "";
            foreach (PS.Model.ExamQuestionType examQuestionTypeModel in examQuestionTypes)
            {
                StringBuilder strIDS = new StringBuilder();
                List<PS.Model.ExamQuestions> SelectQuestions = new List<PS.Model.ExamQuestions>();
                SelectQuestions = (GetRandomQuestions(examQuestionTypeModel.QuestionType, examQuestionTypeModel.QuestionCount.HasValue ? examQuestionTypeModel.QuestionCount.Value : 0, WorkType, WorkLevel));
                foreach (PS.Model.ExamQuestions eq in SelectQuestions)
                {
                    strIDS.Append(eq.ExamQuestionsID + ",");
                }
                if (strIDS.Length > 0)
                {
                    ExamIDString += strIDS.ToString();
                    strSQL.Append(" insert into ExamResult select " + examRecordModel.ExamRecordID + " as ExamRecordID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
                    strSQL.Append(" '" + examRecordModel.UserID + "' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
                    strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
                    strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where ExamQuestions.ValidMark=1 and ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")  ");
                }
            }
            examRecordModel.ExamIDString = ExamIDString.TrimEnd(',');
            ExamIDString = " update ExamRecord set ExamIDString='" + examRecordModel.ExamIDString + "' where ExamRecordID='" + examRecordModel.ExamRecordID + "' ";

            List<string> listStr = new List<string>();
            listStr.Add(strSQL.ToString());
            listStr.Add(ExamIDString);

            int rows = DbHelperSQL.ExecuteSqlTran(listStr);

        }

        //抽题1
        /// <summary>
        /// 包括权重的模拟考试抽题
        /// </summary>
        /// <param name="examRecordModel"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="isManager"></param>
        public void SelectQuestions(PS.Model.ExamRecordPractice examRecordModel, int WorkType, int WorkLevel, bool isManager)
        {
            StringBuilder strSQL = new StringBuilder();
            string ExamIDString = "";
            List<PS.Model.ExamQuestionType> ExamQuestionTypes = (new ExamQuestionType()).GetListModel(" ExamSysParaID='" + examRecordModel.ExamSysParaID.Value + "' Order by disporder ");

            List<PS.Model.ExamQuestions> SelectQuestions = new List<PS.Model.ExamQuestions>();
            SelectQuestions = GetRandomQuestions(ExamQuestionTypes, WorkType, WorkLevel, isManager, examRecordModel.ExamSysParaID.Value);
            List<PS.Model.ExamQuestions> SortQuestions = new List<PS.Model.ExamQuestions>();

            foreach (PS.Model.ExamQuestionType examQuestionTypeModel in ExamQuestionTypes)
            {
                StringBuilder strIDS = new StringBuilder();

                foreach (PS.Model.ExamQuestions eq in SelectQuestions)
                {
                    if (eq.ExamType == examQuestionTypeModel.QuestionType)
                    {
                        strIDS.Append(eq.ExamQuestionsID + ",");
                        SortQuestions.Add(eq);
                    }

                }
                if (strIDS.Length > 0)
                {
                    ExamIDString += strIDS.ToString();
                    strSQL.Append(" insert into ExamResultPractice select " + examRecordModel.ExamRecordPracticeID + " as ExamRecordPracticeID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
                    strSQL.Append(" '" + examRecordModel.UserID + "' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
                    strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
                    strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")   AND ExamQuestions.Valid=1 AND  ExamQuestions.ValidMark=1  ");
                }
            }
            examRecordModel.ExamIDString = ExamIDString.TrimEnd(',');
            ExamIDString = " update ExamRecordPractice set ExamIDString='" + examRecordModel.ExamIDString + "' where ExamRecordPracticeID='" + examRecordModel.ExamRecordPracticeID + "' ";


            List<string> listStr = new List<string>();
            listStr.Add(strSQL.ToString());
            listStr.Add(ExamIDString);

            int rows = DbHelperSQL.ExecuteSqlTran(listStr);

        }

        //抽题1 模拟题
        //public void SelectQuestions(List<PS.Model.ExamQuestionType> examQuestionTypes, PS.Model.ExamRecordPractice examRecordModel, int WorkType, int WorkLevel)
        //{
        //    StringBuilder strSQL = new StringBuilder();
        //    string ExamIDString = "";
        //    foreach (PS.Model.ExamQuestionType examQuestionTypeModel in examQuestionTypes)
        //    {
        //        StringBuilder strIDS = new StringBuilder();
        //        List<PS.Model.ExamQuestions> SelectQuestions = new List<PS.Model.ExamQuestions>();
        //        SelectQuestions = (GetRandomQuestions(examQuestionTypeModel.QuestionType, examQuestionTypeModel.QuestionCount.HasValue ? examQuestionTypeModel.QuestionCount.Value : 0, WorkType, WorkLevel));

        //        foreach (PS.Model.ExamQuestions eq in SelectQuestions)
        //        {
        //            strIDS.Append(eq.ExamQuestionsID + ",");
        //        }
        //        if (strIDS.Length > 0)
        //        {
        //            ExamIDString += strIDS.ToString();
        //            strSQL.Append(" insert into ExamResultPractice select " + examRecordModel.ExamRecordPracticeID + " as ExamRecordPracticeID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
        //            strSQL.Append(" '" + examRecordModel.UserID + "' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
        //            strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
        //            strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where   ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")  ");
        //        }
        //    }
        //    examRecordModel.ExamIDString = ExamIDString.TrimEnd(',');
        //    ExamIDString = " update ExamRecordPractice set ExamIDString='" + examRecordModel.ExamIDString + "' where ExamRecordPracticeID='" + examRecordModel.ExamRecordPracticeID + "' ";

        //    List<string> listStr = new List<string>();
        //    listStr.Add(strSQL.ToString());
        //    listStr.Add(ExamIDString);

        //    int rows = DbHelperSQL.ExecuteSqlTran(listStr);
        //}

        //抽题2 练习题
        public void SelectQuestions(int pageSize, int pageIndex, out long totalCount, PS.Model.ExamRecordPractice examRecordModel, int WorkType, int WorkLevel)
        {

            totalCount = 0;
            StringBuilder strSQL = new StringBuilder();
            string ExamIDString = "";

            StringBuilder strIDS = new StringBuilder();
            StringBuilder strselectSQL = new StringBuilder();
            string deleteSQL = "";

            List<PS.Model.ExamQuestions> SelectQuestions = new List<PS.Model.ExamQuestions>();

            string strWhere = ((WorkType == 0) ? " 1=1 " : " WorkType=" + WorkType) + ((WorkLevel == 0) ? "" : " and WorkLevel=" + WorkLevel);
            string strSql = " Select * from  ExamQuestions Where " + strWhere;
            string totalSql = PageSplitHelper.GetTotalSql(strSql);
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            string pageSql = string.Format(" SELECT TOP {0} * FROM ExamQuestions WHERE(ExamQuestionsID> ISNULL((SELECT MAX(ExamQuestionsID) FROM(SELECT TOP {1} ExamQuestionsID FROM ExamQuestions Where {2} ORDER BY ExamQuestionsID)AST),0 ))AND {2}  ORDER BY ExamQuestionsID",
      pageSize, pageSize * pageIndex, strWhere);

            DataSet ds = DbHelperSQL.Query(pageSql);
            //strselectSQL.Append(" select " + examRecordModel.ExamRecordPracticeID + " as ExamRecordPracticeID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
            //strSQL.Append(" '' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
            //strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
            //strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where ExamQuestions.ValidMark=1 and ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")  ");

            //SelectQuestions =new List<PS.Model.ExamQuestions>();//(GetRandomQuestions(examQuestionTypeModel.QuestionType, examQuestionTypeModel.QuestionCount.HasValue ? examQuestionTypeModel.QuestionCount.Value : 0, WorkType, WorkLevel));
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    strIDS.Append(dr["ExamQuestionsID"] + ",");
                }
                if (strIDS.Length > 0)
                {
                    ExamIDString += strIDS.ToString();
                    strSQL.Append(" insert into ExamResultPractice select " + examRecordModel.ExamRecordPracticeID + " as ExamRecordPracticeID,ExamQuestionsID ,'' as Answer,0 as ExamScore , ");
                    strSQL.Append(" '" + examRecordModel.UserID + "' as  Creator,getdate() as CreateTime,'' as  Editor,getdate() as EditTime ,0 as DispOrder, ");
                    strSQL.Append(" ExamQuestions.ValidMark ,'' as Spare1,'' as Spare2,'' as Spare3,'' as Spare4,null as SpareDecimal5 , ");
                    strSQL.Append(" null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8 from ExamQuestions Where ExamQuestions.ValidMark=1 and ExamQuestions.Valid=1 and ExamQuestions.ExamQuestionsID in " + "(" + (strIDS.ToString()).TrimEnd(',') + ")  ");
                }
            }
            if (!string.IsNullOrEmpty(examRecordModel.ExamIDString) && !string.IsNullOrEmpty(ExamIDString.TrimEnd(',')))
                examRecordModel.ExamIDString += ",";
            examRecordModel.ExamIDString += ExamIDString.TrimEnd(',');
            ExamIDString = " update ExamRecordPractice set ExamIDString='" + examRecordModel.ExamIDString + "' where ExamRecordPracticeID='" + examRecordModel.ExamRecordPracticeID + "' ";

            List<string> listStr = new List<string>();
            listStr.Add(strSQL.ToString());
            listStr.Add(ExamIDString);

            int rows = DbHelperSQL.ExecuteSqlTran(listStr);
        }

        public void DeletePracticeData(int userid)
        {
            string str1 = " Delete ExamResultPractice where creator='" + userid.ToString() + "'";
            string str2 = " Delete ExamRecordPractice where userid='" + userid.ToString() + "'";
            List<string> listStr = new List<string>();
            listStr.Add(str1);
            listStr.Add(str2);
            int rows = DbHelperSQL.ExecuteSqlTran(listStr);
        }

        #endregion  Method



    }
}

